Using EC-SQL-Query as a Data Source

If the source data is taken from the iModel itself, for example from the physical data, then insert a valid EC SQL query in the"eCQuery" property.

The source data is taken from the result set of the EC SQL query.

You can map the column headers and the content in the same manner as an Excel worksheet.

Data

Tag	Class	UserLabel	LongDescription	FederationGuid
FQIT-11-01	MiscEquipment	ACPPEQUIPMENT	NULL	584b2150-8c78-4835-aaec-f211c2419ad6
11IA08	Valve	ACPPPIPEINLINEASSET	Ball Valve, Long Pattern,800 LB, FPT, ASME B16.10	f792f98a-204c-4897-ab71-5c88d09f3db1
T-1130	Tank	ACPPEQUIPMENT	NULL	9d0bb946-efab-4014-be15-a2aeba986c36

Configuration

{
  "id": 5,
  "name": "mapping 5",
  "importType": "smartExcel",
  
  "classKey": "Class",
  "eCQuery": "Select acad.tag, acad.class, comp.userlabel, acad.longDescription, comp.federationGuid FROM nwddynamic.Autocad acad JOIN nwddynamic.nwdcomponent comp ON acad.element.id = comp.EcInstanceId WHERE Not acad.Tag is NULL AND Not acad.Tag LIKE '%?%'",
"relationshipsColMap": [{
	"source": "FederationGUID",
	"relationship": "StagingAreaPhysicalElementFulfillsFunction",
	"target": "EcClassId"
 }
  "data": [
    {
      "sourceClass": "Valve",
      "targetClass": "CONTROL_VALVE",
      "mapping": [
        {
          "source": "Tag",
          "target": "UserLabel"
        },
        {
          "source": "LongDescription",
          "target": "DESCRIPTION"
        }
      ]
    }
  ],
}

Query Example for an Navisworks Dynamic Schema

"eCQuery": "Select acad.tag, acad.class, comp.userlabel, acad.longDescription, comp.federationGuid FROM nwddynamic.Autocad acad JOIN nwddynamic.nwdcomponent comp ON acad.element.id = comp.EcInstanceId WHERE Not acad.Tag is NULL AND Not acad.Tag LIKE '%?%'",

Query Example for an IFC Dynamic Schema

"eCQuery": "SELECT aspect.ifcAKZ as Tag, aspect.Element.Id as EcInstanceId, aspect. ifcOrt as Ort, element.FederationGuid as FederationGUID, element.userlabel as Description, ec_classname(element.ECCLassId,4) as class  FROM IFCDynamic.ifcAspect_EGLV aspect JOIN BisCore.PhysicalElement element ON aspect.element.id = element.EcInstanceId where aspect.ifcAggregat <> '' ",